package cn.sylinx.hbatis.db.common;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.function.Function;

import cn.sylinx.hbatis.db.mapper.ModelBuilder;
import cn.sylinx.hbatis.db.mapper.anno.PrimaryKey;
import cn.sylinx.hbatis.exception.HbatisException;
import cn.sylinx.hbatis.ext.lambda.LambdaUtil;
import cn.sylinx.hbatis.ext.lambda.TypeFunction;
import cn.sylinx.hbatis.kit.StrKit;
import cn.sylinx.hbatis.log.GLog;
import cn.sylinx.hbatis.plugin.model.ModelFabric;

/**
 * fluent sql support, KEY WORD CAN NOT BE USED!
 * 
 * @author han
 *
 * @param <T>
 */
public class FS<T> {

	protected Class<T> modelClass;
	protected StringBuilder conditionSQL = new StringBuilder();
	protected StringBuilder orderBySQL = new StringBuilder();
	protected List<String> excludedColumns = new ArrayList<>(8);
	protected List<Object> paramValues = new ArrayList<>(8);
	protected Map<String, Object> updateColumns = new LinkedHashMap<>(8);
	protected String selectColumns;
	protected boolean distinct = false;
	protected boolean count = false;
	protected String[] primaryKeyColumn;
	protected String tableName;
	protected Map<String, String> attrs = null;
	protected T model;
	protected String preEscape;
	protected String postEscape;

	public static <T> FS<T> of(Class<T> modelClass) {
		return new FS<T>().init(modelClass);
	}

	protected String[] getEscape() {
		return new String[] { "", "" };
	}

	public FS<T> init(Class<T> modelClass) {
		this.parse(modelClass);
		return this;
	}

	public FluentSqlParams<T> build() {

		this.beforeCheck();

		FluentSqlParams<T> fsp = new FluentSqlParams<T>();
		fsp.setConditionSQL(conditionSQL);
		fsp.setModelClass(modelClass);
		fsp.setOrderBy(orderBySQL.toString());
		fsp.setExcludedColumns(excludedColumns);
		fsp.setParamValues(paramValues);
		fsp.setUpdateColumns(updateColumns);
		fsp.setSelectColumns(selectColumns);
		fsp.setPrimaryKeyColumn(primaryKeyColumn);
		fsp.setTableName(tableName);
		fsp.setModel(model);
		fsp.setDistinct(distinct);
		fsp.setCount(count);
		fsp.setAttrs(attrs);
		fsp.setPreEscape(preEscape);
		fsp.setPostEscape(postEscape);

		return fsp;

	}

	private FS<T> parse(Class<T> modelClass) {

		String[] escapes = getEscape();
		this.preEscape = escapes[0];
		this.postEscape = escapes[1];

		this.modelClass = modelClass;
		this.tableName = preEscape + ModelBuilder.getModelFabric(modelClass).getTableName() + postEscape;
		PrimaryKey pk = ModelBuilder.getModelFabric(modelClass).getPrimaryKey();
		this.primaryKeyColumn = pk == null ? new String[] { "id" } : pk.value();
		this.attrs = ModelBuilder.getModelFabric(modelClass).getAttrMapping();
		if (this.attrs == null) {
			this.attrs = new HashMap<>();
		}
		return this;
	}

	public FS<T> exclude(String... columnNames) {

		if (columnNames == null) {
			throw new HbatisException("exclude columnNames is null");
		}

		List<String> columnExcludeList = new ArrayList<>();
		for (int i = 0; i < columnNames.length; ++i) {
			columnExcludeList.add(columnNames[i]);
			columnExcludeList.add(mapColumn(columnNames[i]));
		}

		String[] tempColumnNames = new String[columnExcludeList.size()];
		columnExcludeList.toArray(tempColumnNames);

		Collections.addAll(excludedColumns, tempColumnNames);
		return this;
	}

	@SafeVarargs
	public final <R> FS<T> exclude(TypeFunction<T, R>... lambdaArray) {
		String[] columnNames = new String[lambdaArray.length];
		for (int i = 0; i < lambdaArray.length; ++i) {
			columnNames[i] = LambdaUtil.getLambdaFieldName(lambdaArray[i]);
		}
		return exclude(columnNames);
	}

	public FS<T> select(String columns) {
		if (null != this.selectColumns) {
			throw new HbatisException("Select method can only be called once.");
		}
		this.selectColumns = mapColumns(columns);
		return this;
	}

	public FS<T> select(String... columnArray) {
		if (null != this.selectColumns) {
			throw new HbatisException("Select method can only be called once.");
		}
		this.selectColumns = mapColumns(columnArray);
		return this;
	}

	@SafeVarargs
	public final <R> FS<T> select(TypeFunction<T, R>... lambdaArray) {
		String[] columnNames = new String[lambdaArray.length];
		for (int i = 0; i < lambdaArray.length; ++i) {
			columnNames[i] = LambdaUtil.getLambdaFieldName(lambdaArray[i]);
		}
		return select(columnNames);
	}

	public FS<T> distinct() {
		this.distinct = true;
		return this;
	}

	public FS<T> count() {
		this.count = true;
		return this;
	}

	private String mapColumns(String[] columnArray) {

		this.beforeCheck();
		if (columnArray == null || columnArray.length == 0) {
			return null;
		}
		StringBuilder mappedClumns = new StringBuilder();
		for (String column : columnArray) {
			mappedClumns.append(mapColumn(column.trim())).append(",");
		}
		mappedClumns.deleteCharAt(mappedClumns.length() - 1);
		return mappedClumns.toString();
	}

	private String mapColumns(String columns) {

		this.beforeCheck();
		if (StrKit.isBlank(columns)) {
			return null;
		}
		StringBuilder mappedClumns = new StringBuilder();
		String[] columnArray = columns.split(",");
		for (String column : columnArray) {
			mappedClumns.append(mapColumn(column.trim())).append(",");
		}
		mappedClumns.deleteCharAt(mappedClumns.length() - 1);
		return mappedClumns.toString();
	}

	private String mapColumn(String column) {

		String trueColumn = attrs.get(column);
		return preEscape + (trueColumn == null ? column : trueColumn) + postEscape;
	}

	/**
	 * where condition
	 *
	 * @param column
	 * @return SqlGenerator
	 */
	public FS<T> where(String column) {
		String trueStatement = mapColumn(column);
		conditionSQL.append(" AND ").append(trueStatement);
		return this;
	}

	/**
	 * 
	 * @param lambda
	 * @return
	 */
	public <R> FS<T> where(TypeFunction<T, R> lambda) {
		return where(LambdaUtil.getLambdaFieldName(lambda));
	}

	/**
	 * where condition, simultaneous setting value
	 *
	 * @param column
	 * 
	 * @param value
	 *            column name
	 * @return SqlGenerator
	 */
	public FS<T> where(String column, Object value) {
		String trueStatement = mapColumn(column) + " = ?";
		conditionSQL.append(" AND ").append(trueStatement);
		paramValues.add(value);
		return this;
	}

	/**
	 * 
	 * @param lambda
	 * @param value
	 * @return
	 */
	public <R> FS<T> where(TypeFunction<T, R> lambda, Object value) {
		return where(LambdaUtil.getLambdaFieldName(lambda), value);
	}

	/**
	 * Set the where parameter according to model, and generate sql like where where
	 * age = ? and name = ?
	 *
	 * @param model
	 * @return SqlGenerator
	 */
	public FS<T> where(T model) {

		ModelFabric mf = ModelBuilder.getModelFabric(model.getClass());
		List<Field> declaredFields = mf.getFields();
		Map<String, String> attrs = mf.getAttrMapping();

		try {
			for (Field declaredField : declaredFields) {

				Object value = declaredField.get(model);

				if (null == value) {
					continue;
				}
				if (declaredField.getType().equals(String.class) && StrKit.isBlank(value.toString())) {
					continue;
				}

				String columnName = attrs.get(declaredField.getName());
				this.where(columnName, value);
			}
		} catch (IllegalArgumentException | IllegalAccessException e) {
			GLog.error("where model error:", e);
		}

		return this;
	}

	/**
	 * Equals statement
	 *
	 * @param value
	 *            column value
	 * @return SqlGenerator
	 */
	public FS<T> eq(Object value) {
		conditionSQL.append(" = ?");
		paramValues.add(value);
		return this;
	}

	/**
	 * generate "IS NOT NULL" statement
	 *
	 * @return SqlGenerator
	 */
	public FS<T> notNull() {
		conditionSQL.append(" IS NOT NULL");
		return this;
	}

	/**
	 * generate AND statement, simultaneous setting value
	 *
	 * @param column
	 * @param value
	 *            column value
	 * @return
	 */
	public FS<T> and(String column, Object value) {
		return this.where(column, value);
	}

	/**
	 * 
	 * @param lambda
	 * @param value
	 * @return
	 */
	public <R> FS<T> and(TypeFunction<T, R> lambda, Object value) {
		return and(LambdaUtil.getLambdaFieldName(lambda), value);
	}

	/**
	 * 
	 * @param column
	 * @return
	 */
	public FS<T> and(String column) {
		return this.where(column);
	}

	/**
	 * 
	 * @param lambda
	 * @return
	 */
	public <R> FS<T> and(TypeFunction<T, R> lambda) {
		return this.and(LambdaUtil.getLambdaFieldName(lambda));
	}

	/**
	 * generate OR statement, simultaneous setting value
	 *
	 * @param column
	 * 
	 * @param value
	 *            column value
	 * @return SqlGenerator
	 */
	public FS<T> or(String column, Object value) {

		String trueStatement = mapColumn(column) + " = ?";
		conditionSQL.append(" OR (").append(trueStatement);
		conditionSQL.append(')');
		paramValues.add(value);
		return this;
	}

	/**
	 * 
	 * @param lambda
	 * @param value
	 * @return
	 */
	public <R> FS<T> or(TypeFunction<T, R> lambda, Object value) {
		return or(LambdaUtil.getLambdaFieldName(lambda), value);
	}

	/**
	 * generate "!=" statement, simultaneous setting value
	 *
	 * @param columnName
	 *            column name [sql]
	 * @param value
	 *            column value
	 * @return SqlGenerator
	 */
	public FS<T> andNotEq(String columnName, Object value) {
		conditionSQL.append(" AND ").append(mapColumn(columnName)).append(" != ?");
		paramValues.add(value);
		return this;
	}

	/**
	 * 
	 * @param lambda
	 * @param value
	 * @return
	 */
	public <R> FS<T> andTotEq(TypeFunction<T, R> lambda, Object value) {
		return andNotEq(LambdaUtil.getLambdaFieldName(lambda), value);
	}

	/**
	 * generate "!=" statement, simultaneous setting value
	 *
	 * @param value
	 *            column value
	 * @return SqlGenerator
	 */
	public FS<T> notEq(Object value) {
		conditionSQL.append(" != ?");
		paramValues.add(value);
		return this;
	}

	/**
	 * generate "!= ''" statement
	 *
	 * @param columnName
	 *            column name
	 * @return SqlGenerator
	 */
	public FS<T> andNotEmpty(String columnName) {
		conditionSQL.append(" AND ").append(mapColumn(columnName)).append(" != ''");
		return this;
	}

	/**
	 * 
	 * @param columnName
	 * @return
	 */
	public FS<T> notEmpty(String columnName) {
		conditionSQL.append(mapColumn(columnName)).append(" != ''");
		return this;
	}

	/**
	 * generate "!= ''" statement
	 *
	 * @return
	 */
	public FS<T> notEmpty() {
		conditionSQL.append(" != ''");
		return this;
	}

	/**
	 * generate "IS NOT NULL" statement
	 *
	 * @param columnName
	 *            column name
	 * @return
	 */
	public FS<T> andNotNull(String columnName) {
		conditionSQL.append(" AND ").append(mapColumn(columnName)).append(" IS NOT NULL");
		return this;
	}

	/**
	 * 
	 * @param lambda
	 * @return
	 */
	public <R> FS<T> andNotNull(TypeFunction<T, R> lambda) {
		return andNotNull(LambdaUtil.getLambdaFieldName(lambda));
	}

	/**
	 * generate "IS NOT NULL" statement
	 *
	 * @param columnName
	 *            column name
	 * @return
	 */
	public FS<T> notNull(String columnName) {
		conditionSQL.append(mapColumn(columnName)).append(" IS NOT NULL");
		return this;
	}

	/**
	 * 
	 * @param lambda
	 * @return
	 */
	public <R> FS<T> notNull(TypeFunction<T, R> lambda) {
		return notNull(LambdaUtil.getLambdaFieldName(lambda));
	}

	/**
	 * generate like statement, simultaneous setting value
	 *
	 * @param columnName
	 *            column name
	 * @param value
	 *            column value
	 * @return SqlGenerator
	 */
	public FS<T> andLike(String columnName, Object value) {
		conditionSQL.append(" AND ").append(mapColumn(columnName)).append(" LIKE ?");
		paramValues.add("%" + value + "%");
		return this;
	}

	/**
	 * 
	 * @param lambda
	 * @param value
	 * @return
	 */
	public <R> FS<T> andLike(TypeFunction<T, R> lambda, Object value) {
		return andLike(LambdaUtil.getLambdaFieldName(lambda), value);
	}

	/**
	 * generate like statement, simultaneous setting value
	 *
	 * @param value
	 *            column value
	 * @return SqlGenerator
	 */
	public FS<T> like(Object value) {
		conditionSQL.append(" LIKE ?");
		paramValues.add("%" + value + "%");
		return this;
	}

	/**
	 * generate like statement, simultaneous setting value
	 *
	 * @param columnName
	 *            column name
	 * @param value
	 *            column value
	 * @return SqlGenerator
	 */
	public FS<T> like(String columnName, Object value) {
		String trueColumnName = mapColumn(columnName);
		conditionSQL.append(trueColumnName).append(" LIKE ?");
		paramValues.add("%" + value + "%");
		return this;
	}

	/**
	 * 
	 * @param lambda
	 * @param value
	 * @return
	 */
	public <R> FS<T> like(TypeFunction<T, R> lambda, Object value) {
		return like(LambdaUtil.getLambdaFieldName(lambda), value);
	}

	/**
	 * 
	 * @param columnName
	 * @param value
	 * @return
	 */
	public FS<T> andLikeLeft(String columnName, Object value) {
		String trueColumnName = mapColumn(columnName);
		conditionSQL.append(" AND ").append(trueColumnName).append(" LIKE ?");
		paramValues.add("%" + value);
		return this;
	}

	/**
	 * 
	 * @param lambda
	 * @param value
	 * @return
	 */
	public <R> FS<T> andLikeLeft(TypeFunction<T, R> lambda, Object value) {
		return andLikeLeft(LambdaUtil.getLambdaFieldName(lambda), value);
	}

	/**
	 * 
	 * @param value
	 * @return
	 */
	public FS<T> likeLeft(Object value) {
		conditionSQL.append(" LIKE ?");
		paramValues.add("%" + value);
		return this;
	}

	/**
	 * 
	 * @param columnName
	 * @param value
	 * @return
	 */
	public FS<T> likeLeft(String columnName, Object value) {
		conditionSQL.append(mapColumn(columnName)).append(" LIKE ?");
		paramValues.add("%" + value);
		return this;
	}

	/**
	 * 
	 * @param lambda
	 * @param value
	 * @return
	 */
	public <R> FS<T> likeLeft(TypeFunction<T, R> lambda, Object value) {
		return likeLeft(LambdaUtil.getLambdaFieldName(lambda), value);
	}

	/**
	 * 
	 * @param columnName
	 * @param value
	 * @return
	 */
	public FS<T> andLikeRight(String columnName, Object value) {
		String trueColumnName = mapColumn(columnName);
		conditionSQL.append(" AND ").append(trueColumnName).append(" LIKE ?");
		paramValues.add(value + "%");
		return this;
	}

	/**
	 * 
	 * @param lambda
	 * @param value
	 * @return
	 */
	public <R> FS<T> andLikeRight(TypeFunction<T, R> lambda, Object value) {
		return andLikeRight(LambdaUtil.getLambdaFieldName(lambda), value);
	}

	/**
	 * 
	 * @param value
	 * @return
	 */
	public FS<T> likeRight(Object value) {
		conditionSQL.append(" LIKE ?");
		paramValues.add(value + "%");
		return this;
	}

	/**
	 * 
	 * @param columnName
	 * @param value
	 * @return
	 */
	public FS<T> likeRight(String columnName, Object value) {
		conditionSQL.append(mapColumn(columnName)).append(" LIKE ?");
		paramValues.add(value + "%");
		return this;
	}

	/**
	 * 
	 * @param lambda
	 * @param value
	 * @return
	 */
	public <R> FS<T> likeRight(TypeFunction<T, R> lambda, Object value) {
		return likeRight(LambdaUtil.getLambdaFieldName(lambda), value);
	}

	/**
	 * generate between statement, simultaneous setting value
	 *
	 * @param columnName
	 *            column name
	 * @param a
	 *            first range value
	 * @param b
	 *            second range value
	 * @return SqlGenerator
	 */
	public FS<T> andBetween(String columnName, Object a, Object b) {
		conditionSQL.append(" AND ").append(mapColumn(columnName)).append(" BETWEEN ? and ?");
		paramValues.add(a);
		paramValues.add(b);
		return this;
	}

	/**
	 * generate between statement with lambda, simultaneous setting value
	 *
	 * @param function
	 *            column name with lambda
	 * @param a
	 *            first range value
	 * @param b
	 *            second range value
	 * @param <R>
	 * @return SqlGenerator
	 */
	public <R> FS<T> andBetween(TypeFunction<T, R> lambda, Object a, Object b) {
		return andBetween(LambdaUtil.getLambdaFieldName(lambda), a, b);
	}

	/**
	 * generate between values
	 *
	 * @param a
	 *            first range value
	 * @param b
	 *            second range value
	 * @return SqlGenerator
	 */
	public FS<T> between(Object a, Object b) {
		conditionSQL.append(" BETWEEN ? and ?");
		paramValues.add(a);
		paramValues.add(b);
		return this;
	}

	/**
	 * generate between statement, simultaneous setting value
	 *
	 * @param columnName
	 *            column name
	 * @param a
	 *            first range value
	 * @param b
	 *            second range value
	 * @return SqlGenerator
	 */
	public FS<T> between(String columnName, Object a, Object b) {
		conditionSQL.append(mapColumn(columnName)).append(" BETWEEN ? and ?");
		paramValues.add(a);
		paramValues.add(b);
		return this;
	}

	/**
	 * generate between statement with lambda, simultaneous setting value
	 *
	 * @param function
	 *            column name with lambda
	 * @param a
	 *            first range value
	 * @param b
	 *            second range value
	 * @param <R>
	 * @return SqlGenerator
	 */
	public <R> FS<T> between(TypeFunction<T, R> lambda, Object a, Object b) {
		return between(LambdaUtil.getLambdaFieldName(lambda), a, b);
	}

	/**
	 * generate ">" statement, simultaneous setting value
	 *
	 * @param columnName
	 *            table column name [sql]
	 * @param value
	 *            column value
	 * @return SqlGenerator
	 */
	public FS<T> andGt(String columnName, Object value) {
		String trueColumnName = mapColumn(columnName);
		conditionSQL.append(" AND ").append(trueColumnName).append(" > ?");
		paramValues.add(value);
		return this;
	}

	/**
	 * generate ">" statement with lambda, simultaneous setting value
	 *
	 * @param function
	 *            column name with lambda
	 * @param value
	 *            column value
	 * @param <R>
	 * @return SqlGenerator
	 */
	public <R> FS<T> andGt(TypeFunction<T, R> lambda, Object value) {
		return andGt(LambdaUtil.getLambdaFieldName(lambda), value);
	}

	/**
	 * generate ">" statement value
	 *
	 * @param value
	 *            column value
	 * @return SqlGenerator
	 */
	public FS<T> gt(Object value) {
		conditionSQL.append(" > ?");
		paramValues.add(value);
		return this;
	}

	/**
	 * generate ">" statement, simultaneous setting value
	 *
	 * @param columnName
	 *            table column name [sql]
	 * @param value
	 *            column value
	 * @return SqlGenerator
	 */
	public FS<T> gt(String columnName, Object value) {
		String trueColumnName = mapColumn(columnName);
		conditionSQL.append(trueColumnName).append(" > ?");
		paramValues.add(value);
		return this;
	}

	/**
	 * generate ">" statement with lambda, simultaneous setting value
	 *
	 * @param function
	 *            column name with lambda
	 * @param value
	 *            column value
	 * @param <R>
	 * @return SqlGenerator
	 */
	public <R> FS<T> gt(TypeFunction<T, R> lambda, Object value) {
		return gt(LambdaUtil.getLambdaFieldName(lambda), value);
	}

	/**
	 * generate ">=" statement, simultaneous setting value
	 *
	 * @param column
	 *            table column name [sql]
	 * @param value
	 *            column value
	 * @return SqlGenerator
	 */
	public FS<T> andGte(String column, Object value) {
		String trueColumnName = mapColumn(column);
		conditionSQL.append(" AND ").append(trueColumnName).append(" >= ?");
		paramValues.add(value);
		return this;
	}

	/**
	 * generate ">=" statement, simultaneous setting value
	 * 
	 * @param lambda
	 *            table column name with lambda
	 * @param value
	 * @return
	 */
	public <R> FS<T> andGte(TypeFunction<T, R> lambda, Object value) {
		return andGte(LambdaUtil.getLambdaFieldName(lambda), value);
	}

	/**
	 * generate ">=" statement value
	 *
	 * @param value
	 *            column value
	 * @return SqlGenerator
	 */
	public FS<T> gte(Object value) {
		conditionSQL.append(" >= ?");
		paramValues.add(value);
		return this;
	}

	/**
	 * generate "<" statement, simultaneous setting value
	 *
	 * @param column
	 *            table column name [sql]
	 * @param value
	 *            column value
	 * @return SqlGenerator
	 */
	public FS<T> andLt(String column, Object value) {
		String trueColumnName = mapColumn(column);
		conditionSQL.append(" AND ").append(trueColumnName).append(" < ?");
		paramValues.add(value);
		return this;
	}

	/**
	 * generate "<" statement, simultaneous setting value
	 * 
	 * @param lambda
	 * @param value
	 * @return
	 */
	public <R> FS<T> andLt(TypeFunction<T, R> lambda, Object value) {
		return andLt(LambdaUtil.getLambdaFieldName(lambda), value);
	}

	/**
	 * generate "<" statement value
	 *
	 * @param value
	 *            column value
	 * @return SqlGenerator
	 */
	public FS<T> lt(Object value) {
		conditionSQL.append(" < ?");
		paramValues.add(value);
		return this;
	}

	/**
	 * generate "<=" statement, simultaneous setting value
	 *
	 * @param column
	 *            table column name [sql]
	 * @param value
	 *            column value
	 * @return SqlGenerator
	 */
	public FS<T> andLte(String column, Object value) {
		String trueColumnName = mapColumn(column);
		conditionSQL.append(" AND ").append(trueColumnName).append(" <= ?");
		paramValues.add(value);
		return this;
	}

	/**
	 * generate "<=" statement, simultaneous setting value
	 * 
	 * @param lambda
	 * @param value
	 * @return
	 */
	public <R> FS<T> andLte(TypeFunction<T, R> lambda, Object value) {
		return andLte(LambdaUtil.getLambdaFieldName(lambda), value);
	}

	/**
	 * generate "<=" statement value
	 *
	 * @param value
	 *            column value
	 * @return SqlGenerator
	 */
	public FS<T> lte(Object value) {
		conditionSQL.append(" <= ?");
		paramValues.add(value);
		return this;
	}

	/**
	 * generate ">=" statement, simultaneous setting value
	 *
	 * @param column
	 *            table column name [sql]
	 * @param value
	 *            column value
	 * @return SqlGenerator
	 */
	public FS<T> gte(String column, Object value) {
		conditionSQL.append(mapColumn(column)).append(" >= ?");
		paramValues.add(value);
		return this;
	}

	/**
	 * generate ">=" statement, simultaneous setting value
	 * 
	 * @param lambda
	 *            table column name with lambda
	 * @param value
	 * @return
	 */
	public <R> FS<T> gte(TypeFunction<T, R> lambda, Object value) {
		return gte(LambdaUtil.getLambdaFieldName(lambda), value);
	}

	/**
	 * generate "<" statement, simultaneous setting value
	 *
	 * @param column
	 *            table column name [sql]
	 * @param value
	 *            column value
	 * @return SqlGenerator
	 */
	public FS<T> lt(String column, Object value) {
		String trueColumnName = mapColumn(column);
		conditionSQL.append(trueColumnName).append(" < ?");
		paramValues.add(value);
		return this;
	}

	/**
	 * generate "<" statement, simultaneous setting value
	 * 
	 * @param lambda
	 * @param value
	 * @return
	 */
	public <R> FS<T> lt(TypeFunction<T, R> lambda, Object value) {
		return lt(LambdaUtil.getLambdaFieldName(lambda), value);
	}

	/**
	 * generate "<=" statement, simultaneous setting value
	 *
	 * @param column
	 *            table column name [sql]
	 * @param value
	 *            column value
	 * @return SqlGenerator
	 */
	public FS<T> lte(String column, Object value) {
		String trueColumnName = mapColumn(column);
		conditionSQL.append(trueColumnName).append(" <= ?");
		paramValues.add(value);
		return this;
	}

	/**
	 * generate "<=" statement, simultaneous setting value
	 * 
	 * @param lambda
	 * @param value
	 * @return
	 */
	public <R> FS<T> lte(TypeFunction<T, R> lambda, Object value) {
		return lte(LambdaUtil.getLambdaFieldName(lambda), value);
	}

	/**
	 * generate "in" statement, simultaneous setting value
	 *
	 * @param column
	 *            table column name [sql]
	 * @param args
	 *            column value
	 * @return SqlGenerator
	 */
	public FS<T> andIn(String column, Object... args) {
		if (null == args || args.length == 0) {
			GLog.error("column:{}, in params is empty.", column);
			return this;
		}
		String trueColumnName = mapColumn(column);
		conditionSQL.append(" AND ").append(trueColumnName).append(" IN (");
		this.setArguments(args);
		conditionSQL.append(")");
		return this;
	}

	/**
	 * generate "in" statement, simultaneous setting value
	 *
	 * @param column
	 *            table column name [sql]
	 * @param args
	 *            column value
	 * @return SqlGenerator
	 */
	public FS<T> in(String column, Object... args) {
		if (null == args || args.length == 0) {
			GLog.error("column:{}, in params is empty.", column);
			return this;
		}
		String trueColumnName = mapColumn(column);
		conditionSQL.append(trueColumnName).append(" IN (");
		this.setArguments(args);
		conditionSQL.append(")");
		return this;
	}

	/**
	 * 
	 * @param lambda
	 * @param args
	 * @return
	 */
	public <R> FS<T> in(TypeFunction<T, R> lambda, Object... args) {
		return in(LambdaUtil.getLambdaFieldName(lambda), args);
	}

	/**
	 * 
	 * @param lambda
	 * @param args
	 * @return
	 */
	public <R> FS<T> andIn(TypeFunction<T, R> lambda, Object... args) {
		return andIn(LambdaUtil.getLambdaFieldName(lambda), args);
	}

	/**
	 * generate "in" statement value
	 *
	 * @param args
	 *            column value
	 * @return SqlGenerator
	 */
	public FS<T> in(Object... args) {
		if (null == args || args.length == 0) {
			GLog.error("Column: {}, query params is empty.");
			return this;
		}
		conditionSQL.append(" IN (");
		this.setArguments(args);
		conditionSQL.append(")");
		return this;
	}

	/**
	 * Set in params
	 *
	 * @param list
	 *            in param values
	 * @param <S>
	 * @return SqlGenerator
	 */
	public <S> FS<T> in(List<S> list) {
		return this.in(list.toArray());
	}

	/**
	 * generate "in" statement, simultaneous setting value
	 *
	 * @param column
	 *            column name
	 * @param args
	 *            in param values
	 * @param <S>
	 * @return SqlGenerator
	 */
	public <S> FS<T> in(String column, List<S> args) {
		return this.in(column, args.toArray());
	}

	/**
	 * 
	 * @param lambda
	 * @param args
	 * @return
	 */
	public <R, S> FS<T> in(TypeFunction<T, R> lambda, List<S> args) {
		return this.in(LambdaUtil.getLambdaFieldName(lambda), args);
	}

	/**
	 * generate "in" statement, simultaneous setting value
	 *
	 * @param column
	 *            column name
	 * @param args
	 *            in param values
	 * @param <S>
	 * @return SqlGenerator
	 */
	public <S> FS<T> andIn(String column, List<S> args) {
		return this.andIn(column, args.toArray());
	}

	/**
	 * 
	 * @param lambda
	 * @param args
	 * @return
	 */
	public <R, S> FS<T> andIn(TypeFunction<T, R> lambda, List<S> args) {
		return this.andIn(LambdaUtil.getLambdaFieldName(lambda), args);
	}

	/**
	 * generate order by statement
	 *
	 * @param order
	 *            like "id desc"
	 * @return SqlGenerator
	 */
	public FS<T> order(String order) {
		if (StrKit.isBlank(order)) {
			throw new HbatisException("order is empty");
		}

		if (this.orderBySQL.length() > 0) {
			this.orderBySQL.append(',');
		}

		String orderTemp = order.trim();
		String newOrder = orderTemp;
		String column = null;
		String flag = null;

		int index = orderTemp.indexOf(" ");
		if (index > -1) {
			column = orderTemp.substring(0, index);
			flag = orderTemp.substring(index);
			newOrder = mapColumn(column) + " " + flag;
		}

		this.orderBySQL.append(' ').append(newOrder);
		return this;
	}

	/**
	 * generate order by statement
	 *
	 * @param columnName
	 *            column name
	 * @param orderBy
	 *            order by @see OrderBy
	 * @return SqlGenerator
	 */
	public FS<T> order(String columnName, OrderBy orderBy) {
		if (this.orderBySQL.length() > 0) {
			this.orderBySQL.append(',');
		}
		String trueColumnName = mapColumn(columnName);
		this.orderBySQL.append(' ').append(trueColumnName).append(' ').append(orderBy.toString());
		return this;
	}

	/**
	 * 
	 * @param lambda
	 * @param orderBy
	 * @return
	 */
	public <R> FS<T> order(TypeFunction<T, R> lambda, OrderBy orderBy) {
		return order(LambdaUtil.getLambdaFieldName(lambda), orderBy);
	}

	/**
	 * Update columns set value
	 *
	 * @param column
	 *            column name
	 * @param value
	 *            column value
	 * @return SqlGenerator
	 */
	public FS<T> set(String column, Object value) {
		String trueColumnName = mapColumn(column);
		updateColumns.put(trueColumnName, value);
		return this;
	}

	/**
	 * 
	 * @param lambda
	 * @param value
	 * @return
	 */
	public <R> FS<T> set(TypeFunction<T, R> lambda, Object value) {
		return set(LambdaUtil.getLambdaFieldName(lambda), value);
	}

	public FS<T> set(T model) {
		this.model = model;
		return this;
	}

	/**
	 * Update a model
	 *
	 * @param model
	 *            model instance
	 * @param <S>
	 * @return affect the number of rows
	 */

	private void setArguments(Object[] args) {
		for (int i = 0; i < args.length; i++) {
			if (i == args.length - 1) {
				conditionSQL.append("?");
			} else {
				conditionSQL.append("?, ");
			}
			paramValues.add(args[i]);
		}
	}

	/**
	 * pre check
	 */
	private void beforeCheck() {
		if (null == this.modelClass) {
			throw new HbatisException("model calss is null");
		}
	}

	public FS<T> orNest(Function<FS<T>, FS<T>> func) {
		return or().nest(func);
	}

	public FS<T> andNest(Function<FS<T>, FS<T>> func) {
		return and().nest(func);
	}

	public FS<T> nest(Function<FS<T>, FS<T>> func) {
		conditionSQL.append("(");
		func.apply(this);
		conditionSQL.append(")");
		return this;
	}

	public FS<T> and() {
		conditionSQL.append(" AND ");
		return this;
	}

	public FS<T> or() {
		conditionSQL.append(" OR ");
		return this;
	}

	public FS<T> eq(String column, Object value) {
		conditionSQL.append(mapColumn(column)).append(" = ?");
		paramValues.add(value);
		return this;
	}

	public <R> FS<T> eq(TypeFunction<T, R> lambda, Object value) {
		return eq(LambdaUtil.getLambdaFieldName(lambda), value);
	}

	public FS<T> andEq(String column, Object value) {
		conditionSQL.append(" AND ").append(mapColumn(column)).append(" = ?");
		paramValues.add(value);
		return this;
	}

	public <R> FS<T> andEq(TypeFunction<T, R> lambda, Object value) {
		return andEq(LambdaUtil.getLambdaFieldName(lambda), value);
	}
}
